Query hint management for a database management system

ABSTRACT

Disclosed aspects relate to query hint management for a database management system. With respect to a hint for a query, a set of hint profile data is established. The database management system detects a triggering event. Based on the set of hint profile data, the database management system determines to perform a hint action in response to the triggering event. Performance of the hint action is initiated.

BACKGROUND

This disclosure relates generally to database management systems and,more particularly, relates to query hints. Databases are used to storeinformation for numerous types of applications. Examples include variousindustrial, commercial, technical, scientific, and educationalapplications. Database management systems (DBMSs) are a typicalmechanism for accessing data stored in a database. DBMSs are typicallyconfigured to separate the process of storing data from accessing,manipulating, or using data stored in a database. DBMSs often requiretremendous resources to handle the heavy workloads placed on suchsystems. As such, it may be useful to increase the performance ofdatabase management systems with respect to processing searches, orqueries, to databases.

SUMMARY

Aspects of the disclosure relate to hint life-cycle management, a set ofhint profile data, and usage of the set of hint profile data. The set ofhint profile data can be used to monitor for hints that are not valid,outdated, obsolete, or deprecated. The hint may be updated to reflectupdated status or information. The set of hint profile data may includestatistics such as original estimate with and without the hint, andoriginal run-time with and without the hint. Aspects of the disclosurecan notify a user/administrator when the hint is expired, update thehint automatically without manual intervention, or indicate when a hintmay no longer be valid due to data or code changes.

Disclosed aspects relate to query hint management for a databasemanagement system. With respect to a hint for a query, a set of hintprofile data is established. The database management system detects atriggering event. Based on the set of hint profile data, the databasemanagement system determines to perform a hint action in response to thetriggering event. Performance of the hint action is initiated.

The above summary is not intended to describe each illustratedembodiment or every implementation of the present disclosure.

BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWINGS

The drawings included in the present application are incorporated into,and form part of, the specification. They illustrate embodiments of thepresent disclosure and, along with the description, serve to explain theprinciples of the disclosure. The drawings are only illustrative ofcertain embodiments and do not limit the disclosure.

FIG. 1 illustrates an example representation of a computer systemconnected to a client computer via a network according to an embodiment.

FIG. 2 illustrates an example database management system (DBMS)according to an embodiment.

FIG. 3 is a flowchart illustrating a method for query hint managementaccording to embodiments.

FIG. 4 is a flowchart illustrating a method for query hint managementaccording to embodiments.

FIG. 5 is a flowchart illustrating a method for query hint managementaccording to embodiments.

FIG. 6 is a flowchart illustrating a method for query hint managementaccording to embodiments.

While the invention is amenable to various modifications and alternativeforms, specifics thereof have been shown by way of example in thedrawings and will be described in detail. It should be understood,however, that the intention is not to limit the invention to theparticular embodiments described. On the contrary, the intention is tocover all modifications, equivalents, and alternatives falling withinthe spirit and scope of the invention.

DETAILED DESCRIPTION

Aspects of the disclosure relate to hint life-cycle management, a set ofhint profile data (e.g., hint meta-data), and usage of the set of hintprofile data. The set of hint profile data can be used to monitor forhints that are not valid, outdated, obsolete, or deprecated. The hintitself, which may be embedded as part of the Structured Query Language(SQL) which is further embedded in an application, may be updated toreflect updated status or information. Meta-data related to a query mayinclude users who added the hint, most frequent users of query, releaselevel/fix-level, temporal identifiers for additions/deletions, etc.Meta-data related to a query may include original optimizationstatistics such as original estimate with and without the hint, andoriginal run-time with and without the hint. Aspects of the disclosurecan notify a user/administrator when the hint is expired, update thehint (e.g., beyond circumventing the hint) automatically without manualintervention, or indicate when a hint may no longer be valid due to dataor code changes.

SQL hints may be used for performance/functionality reasons to overrideoptimization decisions that the database optimizer would normally make.For example, a database administrator or application designer may haveintegral information about the database or application that can allowthem to select a specific index which is more efficient than the indexpicked by the optimizer for specific kinds of queries. The hint canforce the selected index (or other plan choices) for those queries.

Query hints may be ubiquitous in database environments, including manyhints across many queries. As such, the system may be cluttered (e.g.,effectively littered) with many hints. Thus, aspects described hereinmay be beneficial to help manage and control what may be termedhint-creep (e.g., the continual addition of hints overtime while losingthe history or the reason for the hint).

For example, a user may utilize a new application which runs a querythat chooses one index, but the user knows the data in their databaseincluding knowing that a different index would run more efficiently. Byadding a hint to the query, the user is able to force the index of theirchoice and continue their application roll-out plans efficiently. Overtime, the database may grow until the hint forces a bad query plan forthe new data in the database tables. The user may investigate untildetermining that a hint is forcing a bad query plan, but the rationalefor having the hint or who added the hint may be unknown. Withoutaspects described herein, the user may end-up spending valuable time andresources to further investigate various features associated with thehint.

Aspects of the disclosure include a method, system, and computer programproduct of query hint management for a database management system. Withrespect to a hint for a query, a set of hint profile data isestablished. The database management system detects a triggering event.Based on the set of hint profile data, the database management systemdetermines to perform a hint action in response to the triggering event.Subsequently, performance of the hint action is initiated.

The hint may include a table scan, an index selection, an indexcriteria, a join order, a join criteria, a statistic, a groupingcriteria, a sorting criteria, a parallelism criteria, a fix-levelcriteria, a database code level, an optimization goal, a fair memoryshare, live data, a processing intensive goal, a parameter markerimplementation, or a materialization goal. In embodiments, the set ofhint profile data with respect to the hint for the query includes a setof historical information, a set of usage information, a set ofperformance information, a set of user information, a set of temporalinformation, a set of computing context information, or a set of eventinformation. The triggering event can include achieving a temporalcriterion, achieving a usage criterion, achieving a performancecriterion, achieving a user criterion, achieving a computing contextcriterion, or achieving an event criterion. In embodiments, the hintaction includes removing the hint, modifying the hint, analyzing thehint, flagging the hint, or notifying a user.

Using query hint management, aspects of the disclosure may provideperformance or efficiency benefits (e.g., speed, flexibility,responsiveness, resource usage, productivity). The optimizer of adatabase management system may benefit from life-cycle management of thehint(s) without requiring manual intervention. Administrators/users ofthe database management system can use their time on matters other thanmanaging hints (e.g., rather than sifting through large data stores ofpast queries/hints). Aspects may save resources such as bandwidth,processing, or memory.

For example, the below underlined text may be an example set of hintprofile data.

A user may provide the following (e.g., query/hint):

SELECT e.last_name,d.department_name

/*+PARALLEL(employees 3)

--user=fred

--system=SYS1

-- Date=07/15/2010 */

FROM employees e, departments d

WHERE e.department_id=d.department_id;

The database management system may intercept it and change to following:

SELECT e.last_name,d.department_name

/*+DB2 Disabled PARALLEL(employees 3)

--user=db2

--system=SYS1

--Date=07/15/2015 */

FROM employees e, departments d

WHERE e.department_id=d.department_id;

In various embodiments, events that may cause a query optimizer toautomatically evaluate hints include a release change, a change to theprogram, a change in query performance beyond a threshold, a change tothe environment, or a requested optimization plan change by databaseoptimizer. The hint may have a specified end or expiration (e.g., enddate, release upgrade, new plan change, hardware upgrade, thresholdnumber of times query is run). Aspects may be performance-specific, andcan include errors that a hint can avoid such as a functional errors. Aquery plan change may be detected, and multiple query plans may run inthe background. If one of the background query plans has a benefit valueexceeding a query plan using the hint, then the query plan using thehint may be disabled and the set of hint profile data can be updated.

One or more reports can be produced (e.g., at the end of the monthcertain hints will expire and the optimizer can recommendremoval/keeping of hints for future). The hint may be honored in oneenvironment but not another (e.g., batch versus interactive); forinstance, the hint may be applied to ALLIO (all rows as fast aspossible) versus FirstIO (first page of rows as fast as possible). Ahistory of hint updates can be logged in the set of hint profile data. Auser/administrator may initiate the reevaluation of the hints at anytime. The set of hint profile data can be stored in the SQL comments, inone or more system catalog tables, or in a data structure on the system.An outdated hint may be completely removed from the SQL; the history orset of hint profile data associated with the removed hint may be storedelsewhere in the system.

FIG. 1 illustrates an example representation of a computer system 100connected to one or more client computers 160 via a network 155,according to some embodiments. For the purposes of this disclosure,computer system 100 may represent practically any type of computer,computer system, or other programmable electronic device, including butnot limited to, a client computer, a server computer, a portablecomputer, a handheld computer, an embedded controller, etc. In someembodiments, computer system 100 may be implemented using one or morenetworked computers, e.g., in a cluster or other distributed computingsystem.

The computer system 100 may include, without limitation, one or moreprocessors (CPUs) 105, a network interface 115, an interconnect 120, amemory 125, and a storage 130. The computer system 100 may also includean I/O device interface 110 used to connect I/O devices 112, e.g.,keyboard, display, and mouse devices, to the computer system 100.

Each processor 105 may retrieve and execute programming instructionsstored in the memory 125 or storage 130. Similarly, the processor 105may store and retrieve application data residing in the memory 125. Theinterconnect 120 may transmit programming instructions and applicationdata between each processor 105, I/O device interface 110, networkinterface 115, memory 125, and storage 130. The interconnect 120 may beone or more busses. The processor 105 may be a single central processingunit (CPU), multiple CPUs, or a single CPU having multiple processingcores in various embodiments. In one embodiment, a processor 105 may bea digital signal processor (DSP).

The memory 125 may be representative of a random access memory, e.g.,Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM),read-only memory, or flash memory. The storage 130 may be representativeof a non-volatile memory, such as a hard disk drive, solid state device(SSD), or removable memory cards, optical storage, flash memory devices,network attached storage (NAS), or connections to storage area network(SAN) devices, or other devices that may store non-volatile data. Thenetwork interface 115 may be configured to transmit data via thecommunications network 155.

The memory 125 may include a database management system (DBMS) 135, aresult set 140, a query 145, and applications 150. Although theseelements are illustrated as residing in the memory 125, any of theelements, or combinations thereof, may reside in the storage 130 orpartially in the memory 125 and partially in the storage 130. Each ofthese elements will be described in greater detail in accordance withFIG. 2.

The network 155 may be any suitable network or combination of networksand may support any appropriate protocol suitable for communication ofdata and/or code to/from the server computer system 100 and the clientcomputer system 160. In some embodiments, the network 155 may supportwireless communications. In other embodiments, the network 155 maysupport hardwired communications. The network 155 may be the Internetand may support Internet Protocol in some embodiments. In otherembodiments, the network 155 may be implemented as a local area network(LAN) or a wide area network (WAN). The network 155 may also beimplemented as a cellular data network. Although the network 155 isshown as a single network in the figures, one or more networks of thesame or different types may be included.

The client computer system 160 may include some or all of the hardwareand software elements of the computer system 100 previously described.As shown, there may be one or more client computers 160 connected to thecomputer system 100 via the network 155. In some embodiments, one ormore client computers 160 may send a query 145 by network 155 tocomputer system 100 and receive a result set 140.

FIG. 2 illustrates an example database management system (DBMS) 135. TheDBMS 135 may include a parser 210, an optimizer 220, an execution engine230, and a database 232. The parser 210 may receive a database query 145from an application 150. In some embodiments, the database query 145 maybe in the form of a Structured Query Language (SQL) statement. Theparser 210 may generate a parsed statement 215. The parser 210 may sendthe parsed statement 215 to an optimizer 220. The optimizer 220 mayattempt to optimize the parsed statement. In some embodiments,optimizing may improve the performance of the database query 145 by, forexample, reducing the amount of time it takes to provide a user with aresponse. The optimizer 220 may generate an execution plan 246 (accessplan), which may be maintained in a query plan cache 245, according tosome embodiments. The query plan cache 245 may include one or moreexecution plans 246, including the current execution plan as well aspreviously used execution plans. Once an execution plan 246 isgenerated, the execution plan 246 may be sent to the execution engine230. The execution engine 230 may execute the query 145. Executing thequery 145 may include finding and retrieving data in the database tables235 that satisfies the criteria supplied in the query 145. The executionengine 230 may store the data returned matching the query 145 in aresult set 140. The DBMS 135 may return the result set 140 to anapplication 150, such as the application in which the database query 145was generated, as a response to the database query 145.

A database 232 may include one or more tables 235 and, in someembodiments, one or more indexes 240. A database table 235 may organizedata into rows and columns. Each row of a database table 235 maycorrespond to an individual entry, a tuple, or a record in the database232. A column may define what is stored in each entry, tuple, or record.In some embodiments, columns of a table 235 may also be referred to asfields or attributes. Each table 235 within the database 232 may have aunique name. Each column within a table 235 may also have a unique name.A row, tuple, or record, however, within a particular table 235 may notbe unique, according to some embodiments. A database 232 may alsoinclude one or more indexes 240. An index 240 may be a data structurethat may inform the DBMS 135 of the location of a particular recordwithin a table 235 if given a particular indexed column value. In someembodiments, the execution engine 230 may use the one or more indexes240 to locate data within a table 235. In other embodiments, theexecution engine 230 may scan the tables 235 without using an index 240.

As mentioned herein, the optimizer 220 creates the query access plan.The optimizer 220 may be implemented as computer program instructionsthat optimize the access plan in dependence upon database managementstatistics. Database statistics may reveal, for example, that there areonly two identification values in a transactions table—so that it is anoptimization, that is, more efficient, to scan the transactions tablerather than using an index. Alternatively, database statistics mayreveal that there are many transaction records with only a fewtransaction records for each identification value—so that it is anoptimization, that is, more efficient, to access the transaction recordsby an index.

FIG. 3 is a flowchart illustrating a method 300 of query hint managementaccording to embodiments. Hints are supported by many databasesincluding those which are key-value store or document-based. Databaseusers/administrators can tailor queries in ways the user/administratordesires by inputting hints into their production environments (e.g.,controlled by the user/administrator). Query hint management, asdescribed herein, may allow a database management system to manage,using a set of hint profile data, a life-cycle of a hint without manualintervention. Method 300 may begin at block 301.

In embodiments, the hint includes a selection of one or more hints froma group. The group can include a table scan (e.g., no index), an indexselection/choice (e.g., which index), an index criteria/implementation(e.g., bitmap, probe, scan), a join order (e.g., of tables), a joincriteria/implementation (e.g., nested loop, hash join, star join, mergejoin), a statistic (e.g., column statistics, frequent values list,histogram, cardinality, index statistics, table statistics, systemstatistics), a grouping criteria/implementation (e.g., index grouping,hash grouping, sort grouping), a sorting/orderingcriteria/implementation (e.g., index sort, temporary table sort), aparallelism criteria, a fix-level criteria, a database code level (e.g.,version, fix, fixpack level, database engine), an optimization goal(e.g., allio firstio), a fair memory share (e.g., a fair RAM share inmegabytes per thread or per query), live data (e.g., insensitive,asensitive, sensitive), a processing intensive goal (e.g., processorversus input-output intensive goal), a parameter marker implementation,or a materialization goal (e.g., subquery, materialized query table,derived table). Various implementations of the hint can include using acost ratio between indexes, for example, in each query plan. Forinstance, if producing an index using the hint in a first query coststwice as much as producing an index without using the hint (or using adifferent hint) in a second query, the second query may be evaluated tobe a better option (when other aspects are equivalent).

At block 330, with respect to a hint for a query, a set of hint profiledata is established. Establishing can include creating, generating,structuring, constructing, forming, organizing, saving, or storing. Forexample, the set of hint profile data may be established/saved/stored(e.g., as meta-data) in the query (e.g., as an SQL comment), in acatalog table, in an application, in a central repository, in anotherdata structure on the system, etc. The set of hint profile data mayinclude meta-data associated with or about the hint/query. When a hintis created/updated, the set of hint profile data may be established(e.g., created/updated). The establishment may occur automatically(e.g., in real-time) without user intervention, or can be related to anotification to a user/administrator. Accordingly, the SQL may berecompiled to capture the update.

In embodiments, the set of hint profile data can have one or more setsof information. The set of hint profile data may include a set ofhistorical information which indicates a set of prior occurrences (e.g.,existing hints, previous hints, deleted hints, reasons/rationales forhints, time-based information of additions/modifications, errors relatedto hints/queries, statistics for hints/queries) at block 332. The set ofhint profile data can include a set of usage information which indicatesa set of operational features (e.g., hardware performance forhints/queries, hardware efficiencies for hints/queries, virtual machineutilization for hints/queries, statistics for hints/queries,user/time-based/system information) at block 333. The set of hintprofile data may have a set of performance information which indicates aset of run-time computations (e.g., expected run-times for queries,original run-times for queries, comparative run-timecomputations/statistics, performance/efficiencies for hints/queries,ratios for queries, statistics for hints/queries) at block 334.

In embodiments, the set of hint profile data may include a set of userinformation which indicates a set of user activities (e.g., acreator-user that created the hint, a modifier-user that modified thehint, a deleter-user that deleted the hint, a utilizer-user thatutilized the hint, a disabler-user that disabled the hint, afrequent-user factor) at block 335. The set of hint profile data caninclude a set of temporal information which indicates a set ofevent-times (e.g., when hints were used, when hints were not used, whenqueries were initiated, when queries were completed, how long it took torun queries, when errors occurred, when hints expired, when hints werecreated, when hints were deleted, when hints were modified, when thevalidity/usefulness of a hint came into question, on-peak/off-peaktiming) at block 336. The set of hint profile data may have a set ofcomputing context information which indicates a set of computing factors(e.g., a computing environment, a batch/ALLIO environment, aninteractive/FirstIO environment, a computing assetversion/release/fix-level, a software/application/program, computinghardware such as processor/memory, network availability/bandwidth, alist/database of systems that have or have not been updated, a programcode update or update schedule) at block 337. The set of hint profiledata can have a set of event information which indicates a set ofstate-conditions (e.g., a triggering event, a computing assetversion/release change, a query performance change exceeding athreshold, a query plan change or change request, a computingenvironment change, a hint end/expiration date, a release/hardwareupgrade, a count of query executions with respect to a threshold, errorsencountered, SQL error return codes, error message and offset fromapplication) at block 338.

At block 350, the database management system detects a triggering event.Detecting can include receiving (e.g., from a user, from anapplication), sensing (e.g., in a multidimensional array, in anapplication), observing (e.g., based on usage/utilization). Thetriggering event may include achieving a temporal criterion (e.g., atime-to-live, an expiration date/time for a hint/query, anon-peak/off-peak period, a usage time, a hint-reevaluation date/time, anoriginal run-time with respect to an actual run-time, an expectedrun-time with respect to an actual run-time) at block 352. Thetriggering event can include achieving a usage criterion (e.g., a countof query executions with respect to a threshold, a type-of-queryutilization, execution/non-execution with respect to a specificdatabase, a computing hardware resource exceeding a threshold, anapplication/environment hint/query utilization quota) at block 353. Thetriggering event may include achieving a performance criterion (e.g.,statistics for hints/queries, a statistical benefit calculation, acomparison of the query with and without the hint, a processing timeexceeding a threshold, a computing hardware performance statistic, anerror rate, expected run-times for queries, original run-times forqueries, comparative run-time computations/statistics,performance/efficiencies for hints/queries, ratios for queries) at block354.

In embodiments, the triggering event can include achieving a usercriterion (e.g., a creator-user that created the hint, a modifier-userthat modified the hint, a deleter-user that deleted the hint, autilizer-user that utilized the hint, a disabler-user that disabled thehint, a frequent-user factor) at block 355. The triggering event mayinclude achieving a computing context criterion (e.g., a computingenvironment, a batch/ALLIO environment, an interactive/FirstIOenvironment, a computing asset version/release/fix-level, asoftware/application/program, computing hardware, networkavailability/bandwidth, a list/database of systems that have or have notbeen updated, a program code update or update schedule) at block 356.The triggering event can include achieving an event criterion (e.g., athreshold number of errors, a percentage of queries processedwith/without hints, a computing asset version/release change, a queryperformance change exceeding a threshold, a query plan change or changerequest, a computing environment change, a hint end/expiration date, arelease/hardware upgrade, a count of query executions with respect to athreshold, errors encountered, SQL error return codes) at block 357.

At block 360, based on the set of hint profile data, the databasemanagement system determines/resolves/computes toperform/execute/carry-out a hint action in response to the triggeringevent. For example, elements of the set of hint profile data may becompared/evaluated with respect to an associated threshold element(e.g., hardware resource utilization such as processor running at 85%with a threshold of 80%). When the element exceeds the associatedthreshold element, the database management system determines to performthe hint action. In other examples, the hint action is performed whenthe associated threshold element exceeds the element (e.g.,version/release number such as the current threshold release is 3.1 andthe release assumed by the hint/query is 1.0).

In embodiments, the hint action may include removing the hint (e.g.,taking the hint out of the query, disabling the hint from usage by thequery) at block 361. The hint action can include modifying the hint(e.g., altering the hint to better reflect the contents of the set ofhint profile data, altering the hint for a current/new/expected usage,changing the hint for a different software version, a different hint fora different environment, preventing usage of the hint in certainpredefined configurations) at block 362. The hint action may includeanalyzing the hint (e.g.,extracting/examining/evaluating/inspecting/classifying/categorizing thehint without or with respect to other hints/queries, producing a reportof hints such as those which are configured to expire within a thresholdperiod of time) at block 363.

In embodiments, the hint action can include flagging the hint (e.g., fora user/administrator to analyze it at a later time, for the databasemanagement system to analyze at a later or less-burdensome time withoutmanual intervention, identifying that the hint has significant positiveimpacts relative to the query without the hint, noting that the databasethe hint refers to is set to expire) at block 364. The hint action mayinclude notifying a user (e.g., sending an e-mail or instant message,popping-up a dialog box, highlighting with a distinct color the query orquery plan, presenting benefits/burdens of the hint, displaying ahistory of the hint, providing a hint reply list of actions such ashonor the hint, reevaluate the hint, cancel the hint, or override thehint with respect to one or more particular jobs) at block 365.

At block 380, performance of the hint action is initiated. Initiatingperformance can include starting execution, beginning to carry-out aprocess, etc. The hint action may be fullyperformed/executed/carried-out. For example, the hint action can includemodifying the hint to indicate the query to use a first query accessplan instead of a second query access plan. Without modification, thequery is indicated by the hint to use the second query access plan(e.g., thus using the first query access plan has expectedperformance/efficiency benefits). In particular, a first index may beused to produce a valid results-set instead of a second index. Variousof possibilities are contemplated.

To illustrate, because structured query language (SQL) is declarative,there are typically multiple different ways to execute a given query(e.g., query plans) and each of those ways yields a differentperformance (e.g., while arriving at the same results). A query plan caninclude an ordered sequence of steps used to access or modifyinformation in SQL based data systems. A typical query optimizerconsiders candidate query plans for a given query and determines whichof those query plans will be efficient (e.g., lesser amount of latency).The query plan may be processed for storage (e.g., in a data store) orpresentation (e.g., via a display). In embodiments, processing the queryplan includes providing a valid results-set. Providing the validresults-set can include routing the valid results-set to the originator,processing an output with respect to the valid results-set, returningthe valid results-set to storage, or displaying at least a portion of avalid results-set (e.g., to a user).

Method 300 concludes at block 399. Aspects of method 300 may provideperformance or efficiency benefits with respect to query hintmanagement. For example, aspects of method 300 may include positiveimpacts on resource usage (e.g., memory) or speed (e.g., processing) byusing a hint action. Altogether, performance or efficiency benefitsrelated to hint management for future processing of queries may occur(e.g., flexibility, responsiveness, productivity).

FIG. 4 is a flowchart illustrating a method 400 of query hint managementaccording to embodiments. Query hint management, as described herein,may allow a database management system to manage, using a set of hintprofile data, a life-cycle of a hint without manual intervention.Aspects of method 400 may be similar to or the same as method 300, andelements/features herein can be utilized interchangeably (e.g., withrespect to the database management system). Method 400 may begin atblock 401.

In embodiments, the database management system monitors the hint for thequery to collect the set of hint profile data at block 411. Monitoringthe hint can include tracking usage of the hint. Various daemons (e.g.,which may run in the background) may be utilized/formulated to track thehint (e.g., by probing queries having the hint). The set of hint profiledata may be collected at block 421 (e.g., to establish the set of hintprofile data in a data store/structure). Collecting can includeaccumulating, acquiring, aggregating, capturing, gathering, obtaining,saving, or storing. The collecting can occur in an on-going (e.g.,real-time, streamlined) basis or periodically (e.g., once per day). Atblock 430, with respect to a hint for a query, a set of hint profiledata is established.

In embodiments, the database management system monitors the set of hintprofile data at block 442 to ascertain whether the set of hint profiledata indicates achievement of the triggering event. In embodiments, bythe database management system monitors a set of computing factors(e.g., a computing environment, a batch/ALLIO environment, aninteractive/FirstIO environment, a computing assetversion/release/fix-level, a software/application/program, computinghardware such as processor/memory, network availability/bandwidth, alist/database of systems that have or have not been updated, a programcode update or update schedule) at block 443 to ascertain whether theset of computing factors indicates achievement of the triggering event.Monitoring can include observing and analyzing. In certain embodiments,observing and analyzing can include aspects which detect/receive anelement and process/identify the element that was detected/received. Forinstance, observing can include querying (e.g., asking a question),searching (e.g., exploring for a reason), obtaining (e.g., recording acollection), probing (e.g., checking a property), scanning (e.g.,reviewing a sample), or tracking (e.g., following a characteristic). Forinstance, analyzing can include extracting (e.g., creating aderivation), examining (e.g., performing an inspection), dissecting(e.g., scrutinizing an attribute), parsing (e.g., deciphering aconstruct), comparing (e.g., relating an assessment), or classifying(e.g., assigning a designation). Data analysis may include a process ofinspecting, cleaning, transforming, or modeling data to discover usefulinformation, suggest conclusions, or support decisions. Data analysiscan extract information/patterns from a data set and transform/translateit into an understandable structure (e.g., a data report which can beprovided) for further use.

At block 450, the database management system detects a triggering event.At block 460, based on the set of hint profile data, the databasemanagement system determines to perform a hint action in response to thetriggering event. At block 480, performance of the hint action isinitiated.

Method 400 concludes at block 499. Aspects of method 400 may provideperformance or efficiency benefits with respect to query hintmanagement. For example, aspects of method 400 may include positiveimpacts on resource usage (e.g., memory) or speed (e.g., processing) bymonitoring hints, hint profile data, or computing factors. Altogether,performance or efficiency benefits related to using a hint action mayoccur (e.g., flexibility, responsiveness, productivity).

FIG. 5 is a flowchart illustrating a method 500 of query hint managementaccording to embodiments. Query hint management, as described herein,may allow a database management system to manage, using a set of hintprofile data, a life-cycle of a hint without manual intervention.Aspects of method 500 may be similar to or the same as methods 300 and400, and elements/features herein can be utilized interchangeably (e.g.,with respect to the database management system). Method 500 may begin atblock 501.

At block 530, with respect to a hint for a query, a set of hint profiledata is established. At block 550, the database management systemdetects a triggering event. At block 560, based on the set of hintprofile data, the database management system determines to perform ahint action in response to the triggering event.

In response to an execution of the query with the hint, a hint-includedmeasurement value may be calculated at block 571. Execution of the querymay occur before, after, or as the triggering event. The hint-includedmeasurement value may have a statistical value, ratio, or othernumerical value which indicates a performance, efficiency, or output ofexecuting the query with the hint (e.g., processor usage, memory usage,bandwidth usage, run-time, energy usage, achieving desired output). Thequery without the hint can be compiled at block 572. In response to anexecution of the query without the hint, a hint-excluded measurementvalue may be calculated at block 573. The hint-excluded measurementvalue may have a statistical value, ratio, or other numerical valuewhich indicates a performance, efficiency, or output of executing thequery without the hint.

The hint-included performance measurement value and the hint-excludedperformance measurement value may be compared at block 574 (e.g., todetermine if one is preferred over the other and possibly how muchbetter/preferred). In certain embodiments, whether the hint-includedperformance measurement value and the hint-excluded performancemeasurement value are within a threshold difference value (e.g., within10%, within 5 units, within 2 seconds of processing time) can becomputed at block 579. Based on the comparing, the hint action can bedetermined at block 575. For example, the hint may be removed if thehint-excluded performance measurement value is computed twice as fast asthe hint-included performance measurement value (e.g., with no othersignificant downsides). As another example, the hint may be flagged ifthe hint-excluded performance measurement value is computed slightlyfaster than the hint-included performance measurement value (e.g.,allowing a user/administrator to later look at the flagged hint to makea decision without having the database management system automaticallychanging elements that may produce other unexpected challenges). Otherpossibilities including those using elements described herein usingvarious combinations of comparison techniques and hint actions areconsidered. At block 580, performance of the hint action is initiated.

Method 500 concludes at block 599. Aspects of method 500 may provideperformance or efficiency benefits with respect to query hintmanagement. For example, aspects of method 500 may include positiveimpacts on resource usage (e.g., memory) or speed (e.g., processing) byselecting which queries to use or not use hints in. Altogether,performance or efficiency benefits related to using a hint action mayoccur (e.g., flexibility, responsiveness, productivity).

FIG. 6 is a flowchart illustrating a method 600 of query hint managementaccording to embodiments. Query hint management, as described herein,may allow a database management system to manage, using a set of hintprofile data, a life-cycle of a hint without manual intervention.Aspects of method 600 may be similar to or the same as methods 300, 400,and 500, and elements/features herein can be utilized interchangeably(e.g., with respect to the database management system). Method 600 maybegin at block 601.

At block 630, with respect to a hint for a query, a set of hint profiledata is established. At block 650, the database management systemdetects a triggering event. At block 660, based on the set of hintprofile data, the database management system determines to perform ahint action in response to the triggering event. At block 680,performance of the hint action is initiated.

In embodiments, the hint action includes adding a new query without thehint at block 681. A set of computing factors (e.g., a computingenvironment, a batch/ALLIO environment, an interactive/FirstIOenvironment, a computing asset version/release/fix-level, asoftware/application/program, computing hardware such asprocessor/memory, network availability/bandwidth, a list/database ofsystems that have or have not been updated, a program code update orupdate schedule) is detected at block 682. Based on the set of computingfactors (e.g., by comparing corresponding computing factors for thequeries and choosing/selecting based on performance/efficiency), it isdetermined whether to process the query or the new query at block 683.Based on the determining, at least one of the query or the new query isprocessed/executed/run at block 684.

Method 600 concludes at block 699. Aspects of method 600 may provideperformance or efficiency benefits with respect to query hintmanagement. For example, aspects of method 600 may include positiveimpacts on resource usage (e.g., memory) or speed (e.g., processing) byusing a set of computing factors to determine whether to process thequery or the new query. Altogether, performance or efficiency benefitsrelated to using a hint action may occur (e.g., flexibility,responsiveness, productivity).

In addition to embodiments described above, other embodiments havingfewer operational steps, more operational steps, or differentoperational steps are contemplated. Also, some embodiments may performsome or all of the above operational steps in a different order. Themodules are listed and described illustratively according to anembodiment and are not meant to indicate necessity of a particularmodule or exclusivity of other potential modules (or functions/purposesas applied to a specific module).

In the foregoing, reference is made to various embodiments. It should beunderstood, however, that this disclosure is not limited to thespecifically described embodiments. Instead, any combination of thedescribed features and elements, whether related to differentembodiments or not, is contemplated to implement and practice thisdisclosure. Many modifications and variations may be apparent to thoseof ordinary skill in the art without departing from the scope and spiritof the described embodiments. Furthermore, although embodiments of thisdisclosure may achieve advantages over other possible solutions or overthe prior art, whether or not a particular advantage is achieved by agiven embodiment is not limiting of this disclosure. Thus, the describedaspects, features, embodiments, and advantages are merely illustrativeand are not considered elements or limitations of the appended claimsexcept where explicitly recited in a claim(s).

The present invention may be a system, a method, and/or a computerprogram product. The computer program product may include a computerreadable storage medium (or media) having computer readable programinstructions thereon for causing a processor to carry out aspects of thepresent invention.

The computer readable storage medium can be a tangible device that canretain and store instructions for use by an instruction executiondevice. The computer readable storage medium may be, for example, but isnot limited to, an electronic storage device, a magnetic storage device,an optical storage device, an electromagnetic storage device, asemiconductor storage device, or any suitable combination of theforegoing. A non-exhaustive list of more specific examples of thecomputer readable storage medium includes the following: a portablecomputer diskette, a hard disk, a random access memory (RAM), aread-only memory (ROM), an erasable programmable read-only memory (EPROMor Flash memory), a static random access memory (SRAM), a portablecompact disc read-only memory (CD-ROM), a digital versatile disk (DVD),a memory stick, a floppy disk, a mechanically encoded device such aspunch-cards or raised structures in a groove having instructionsrecorded thereon, and any suitable combination of the foregoing. Acomputer readable storage medium, as used herein, is not to be construedas being transitory signals per se, such as radio waves or other freelypropagating electromagnetic waves, electromagnetic waves propagatingthrough a waveguide or other transmission media (e.g., light pulsespassing through a fiber-optic cable), or electrical signals transmittedthrough a wire.

Computer readable program instructions described herein can bedownloaded to respective computing/processing devices from a computerreadable storage medium or to an external computer or external storagedevice via a network, for example, the Internet, a local area network, awide area network and/or a wireless network. The network may comprisecopper transmission cables, optical transmission fibers, wirelesstransmission, routers, firewalls, switches, gateway computers and/oredge servers. A network adapter card or network interface in eachcomputing/processing device receives computer readable programinstructions from the network and forwards the computer readable programinstructions for storage in a computer readable storage medium withinthe respective computing/processing device.

Computer readable program instructions for carrying out operations ofthe present invention may be assembler instructions,instruction-set-architecture (ISA) instructions, machine instructions,machine dependent instructions, microcode, firmware instructions,state-setting data, or either source code or object code written in anycombination of one or more programming languages, including an objectoriented programming language such as Java, Smalltalk, C++ or the like,and conventional procedural programming languages, such as the “C”programming language or similar programming languages. The computerreadable program instructions may execute entirely on the user'scomputer, partly on the user's computer, as a stand-alone softwarepackage, partly on the user's computer and partly on a remote computeror entirely on the remote computer or server. In the latter scenario,the remote computer may be connected to the user's computer through anytype of network, including a local area network (LAN) or a wide areanetwork (WAN), or the connection may be made to an external computer(for example, through the Internet using an Internet Service Provider).In some embodiments, electronic circuitry including, for example,programmable logic circuitry, field-programmable gate arrays (FPGA), orprogrammable logic arrays (PLA) may execute the computer readableprogram instructions by utilizing state information of the computerreadable program instructions to personalize the electronic circuitry,in order to perform aspects of the present invention.

Aspects of the present invention are described herein with reference toflowchart illustrations and/or block diagrams of methods, apparatus(systems), and computer program products according to embodiments of theinvention. It will be understood that each block of the flowchartillustrations and/or block diagrams, and combinations of blocks in theflowchart illustrations and/or block diagrams, can be implemented bycomputer readable program instructions.

These computer readable program instructions may be provided to aprocessor of a general purpose computer, special purpose computer, orother programmable data processing apparatus to produce a machine, suchthat the instructions, which execute via the processor of the computeror other programmable data processing apparatus, create means forimplementing the functions/acts specified in the flowchart and/or blockdiagram block or blocks. These computer readable program instructionsmay also be stored in a computer readable storage medium that can directa computer, a programmable data processing apparatus, and/or otherdevices to function in a particular manner, such that the computerreadable storage medium having instructions stored therein comprises anarticle of manufacture including instructions which implement aspects ofthe function/act specified in the flowchart and/or block diagram blockor blocks.

The computer readable program instructions may also be loaded onto acomputer, other programmable data processing apparatus, or other deviceto cause a series of operational steps to be performed on the computer,other programmable apparatus or other device to produce a computerimplemented process, such that the instructions which execute on thecomputer, other programmable apparatus, or other device implement thefunctions/acts specified in the flowchart and/or block diagram block orblocks.

Embodiments according to this disclosure may be provided to end-usersthrough a cloud-computing infrastructure. Cloud computing generallyrefers to the provision of scalable computing resources as a serviceover a network. More formally, cloud computing may be defined as acomputing capability that provides an abstraction between the computingresource and its underlying technical architecture (e.g., servers,storage, networks), enabling convenient, on-demand network access to ashared pool of configurable computing resources that can be rapidlyprovisioned and released with minimal management effort or serviceprovider interaction. Thus, cloud computing allows a user to accessvirtual computing resources (e.g., storage, data, applications, and evencomplete virtualized computing systems) in “the cloud,” without regardfor the underlying physical systems (or locations of those systems) usedto provide the computing resources.

Typically, cloud-computing resources are provided to a user on apay-per-use basis, where users are charged only for the computingresources actually used (e.g., an amount of storage space used by a useror a number of virtualized systems instantiated by the user). A user canaccess any of the resources that reside in the cloud at any time, andfrom anywhere across the Internet. In context of the present disclosure,a user may access applications or related data available in the cloud.For example, the nodes used to create a stream computing application maybe virtual machines hosted by a cloud service provider. Doing so allowsa user to access this information from any computing system attached toa network connected to the cloud (e.g., the Internet).

Embodiments of the present disclosure may also be delivered as part of aservice engagement with a client corporation, nonprofit organization,government entity, internal organizational structure, or the like. Theseembodiments may include configuring a computer system to perform, anddeploying software, hardware, and web services that implement, some orall of the methods described herein. These embodiments may also includeanalyzing the client's operations, creating recommendations responsiveto the analysis, building systems that implement portions of therecommendations, integrating the systems into existing processes andinfrastructure, metering use of the systems, allocating expenses tousers of the systems, and billing for use of the systems.

The flowchart and block diagrams in the Figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods, and computer program products according to variousembodiments of the present invention. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof instructions, which comprises one or more executable instructions forimplementing the specified logical function(s). In some alternativeimplementations, the functions noted in the block may occur out of theorder noted in the figures. For example, two blocks shown in successionmay, in fact, be executed substantially concurrently, or the blocks maysometimes be executed in the reverse order, depending upon thefunctionality involved. It will also be noted that each block of theblock diagrams and/or flowchart illustration, and combinations of blocksin the block diagrams and/or flowchart illustration, can be implementedby special purpose hardware-based systems that perform the specifiedfunctions or acts or carry out combinations of special purpose hardwareand computer instructions.

While the foregoing is directed to exemplary embodiments, other andfurther embodiments of the invention may be devised without departingfrom the basic scope thereof, and the scope thereof is determined by theclaims that follow. The descriptions of the various embodiments of thepresent disclosure have been presented for purposes of illustration, butare not intended to be exhaustive or limited to the embodimentsdisclosed. Many modifications and variations will be apparent to thoseof ordinary skill in the art without departing from the scope and spiritof the described embodiments. The terminology used herein was chosen toexplain the principles of the embodiments, the practical application ortechnical improvement over technologies found in the marketplace, or toenable others of ordinary skill in the art to understand the embodimentsdisclosed herein.

What is claimed is:
 1. A computer-implemented method of query hintmanagement for a database management system, the method comprising:establishing a set of hint profile data with respect to a hint for aquery; detecting, by the database management system, a triggering event;determining, by the database management system based on the set of hintprofile data, to perform a hint action in response to the triggeringevent; initiating performance of the hint action, with the hint actionincluding adding a new query without the hint; detecting a set ofcomputing factors; determining, based on the set of computing factors,whether to process the query or the new query; and processing, based onthe determination, at least one of the query or the new query.